﻿-- =============================================
-- Author:		Dmitry NAzarov
-- Create date: 29.03.2011
-- Description:	Отчет Оплаченные изделия
-- =============================================
CREATE PROCEDURE [dbo].[report_paid_products] 
	@p_nsi_type_of_activity_id int,
	@p_nsi_organization_id int,
	@p_contract_id	int,
	@p_xml_products xml,
	@p_curreny_year bit
AS
BEGIN
	SET NOCOUNT ON;
	
	declare @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xml_products

    SELECT	c.ID as CONTRACT_ID,
			pu.NAME as PRODUCT_NAME, 
			o.SHORT_NAME as ORGANIZATION_NAME, 
			c.NUMBER as CONTRACT_NUMBER,
			--cs.END_DATE as CP_STAGE_END_DATE,
			SUM(pu.PAYMENT_IN_SUM_CUR) * 100 / SUM(pc.COST) as PAYMEN_PERCENTAGE,
			dbo.f_subreport_paid_products(c.ID, @p_curreny_year, cp.NSI_CURRENCY_ID, NULL, pu.NAME) as PAYMENT_DATE,
			-- эти значения беруться из подзапроса
			SUM([pi].PAY_SUM) as PAID_RUR,
			dbo.f_convert_to_rur(SUM(pc.COST), getdate(), cp.NSI_CURRENCY_ID) - SUM(pu.PAYMENT_IN_SUM_RUB) as REST_SUM_RUR,
			COUNT(*) as PRODUCT_COUNT, pu.CP_STAGE_END_DATE as STAGE_END_DATE
    FROM VIEW_SEARCH_P_U_FOR_FORMS as pu
    INNER JOIN CP_STAGE cs ON cs.ID = pu.CP_STAGE_ID
    INNER JOIN PID ON PID.ID = [pu].PID
	INNER JOIN [CONTRACT] as c ON c.ID = PID.CONTR_ID
	INNER JOIN NSI_ORGANIZATION as o ON o.ID = c.NSI_ORGANIZATION_ID
	--Подзапрос, выбирающий все ID единиц продукции (PRODUCT_UNIT) с оплатой по эти продуктам за определенный период
	INNER JOIN (SELECT pit.PRODUCT_UNIT_ID as PU_ID, SUM(pit.SUM_RUB) as PAY_SUM FROM PAYMENT_IN as p INNER JOIN PAYMENT_IN_ITEM as pit ON p.ID = pit.PAYMENT_IN_ID WHERE (@p_curreny_year = 0) OR
                      (YEAR(p.DATE) = YEAR(GETDATE())) 
					  GROUP BY pit.PRODUCT_UNIT_ID
					   ) as [pi] ON [pi].PU_ID = pu.ID
	
	--INNER JOIN PAYMENT_IN_ITEM as pii ON pu.ID = pii.PRODUCT_UNIT_ID
	--INNER JOIN PAYMENT_IN as [pi] ON [pi].ID = pii.PAYMENT_IN_ID
	INNER JOIN PRODUCT_COST as pc ON pu.ID = pc.PRODUCT_UNIT_ID
	INNER JOIN COST_PROTOCOL as cp ON [cp].ID = pc.COST_PROTOCOL_ID
    --INNER JOIN CALENDAR_PLAN as cap ON cap.ID = cs.CALENDAR_PLAN_ID
    
    WHERE pu.NSI_PRODUCT_STATE_ID <= 2 AND pu.TYPE = 1
    AND
    (
		(@p_nsi_type_of_activity_id is null)
		OR
		(c.NSI_TYPE_OF_ACTIVITY_ID = @p_nsi_type_of_activity_id)
    )
    AND
    (
		(@p_nsi_organization_id is null)
		OR
		(c.NSI_ORGANIZATION_ID = @p_nsi_organization_id)
    )
     AND
    (
		(@p_contract_id is null)
		OR
		(c.ID = @p_contract_id)
    )
    AND
		(
			(@p_xml_products is null)
			OR
			(
				pu.NSI_PRODUCT_ID IN (				
				SELECT id FROM  OPENXML(@docHandle, N'/products/product') WITH (id int)
				)
			)
		)
	
    GROUP BY pu.NAME, pu.CP_STAGE_END_DATE,o.SHORT_NAME, c.NUMBER, cp.NSI_CURRENCY_ID, c.ID
    
    EXEC sp_xml_removedocument @docHandle
END